﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace Citi.HeadCount.WebUI.Util
{
    public class ExcelReader : IDisposable
    {
        private readonly bool connectionString;
        private readonly OleDbConnection oleDbConnection;

        #region Constructors

        public ExcelReader(string path)
            : this(path, true, true)
        {
        }

        public ExcelReader(string path, bool intermixedRead)
            : this(path, intermixedRead, true)
        {
        }

        public ExcelReader(string path, bool intermixedRead, bool firstRowIsHeader)
        {
            if (string.IsNullOrEmpty(path))
                throw new ArgumentException("path");

            oleDbConnection = ExcelHelper.CreateConnection(path, intermixedRead, firstRowIsHeader);
        }

        public ExcelReader(string path, bool useConnectionString, bool intermixedRead, bool firstRowIsHeader,
                           bool isOledb12)
        {
            connectionString = useConnectionString;
            if (string.IsNullOrEmpty(path))
                throw new ArgumentException("path");

            oleDbConnection = isOledb12
                              ? ExcelHelper.CreateConnectionOledb12(path, intermixedRead, firstRowIsHeader)
                              : ExcelHelper.CreateConnection(path, intermixedRead, firstRowIsHeader);
        }

        #endregion

        #region Destructors

        public void Dispose()
        {
            if (oleDbConnection == null) return;

            if (oleDbConnection.State != ConnectionState.Closed)
            {
                try
                {
                    oleDbConnection.Close();
                }
                catch
                {
                }
            }
            oleDbConnection.Dispose();
        }

        #endregion

        #region GetData Members

        public DataTable GetData()
        {
            string[] fileSheets = GetSheetNames();
            return GetData(fileSheets[0]);
        }
        public DataTable GetData(string sheetName)
        {
            return GetData(sheetName, false);
        }
        public DataTable GetData(string sheetName, bool ignoreNullRows)
        {
            if (string.IsNullOrEmpty(sheetName))
                throw new ArgumentException("sheetName");

            var dataTable = new DataTable(sheetName);

            var ignoreStatement = "";
            if (ignoreNullRows)
            {
                var columns = ExcelHelper.GetSheetDataColumns(oleDbConnection, sheetName);

                var sbIgnore = new StringBuilder();
                foreach (var column in columns)
                {
                    sbIgnore.Append(sbIgnore.Length == 0 ? " WHERE " : " OR ");

                    sbIgnore.Append("[");
                    sbIgnore.Append(column.ColumnName);
                    sbIgnore.Append("]");

                    sbIgnore.Append(" IS NOT NULL");
                }
                ignoreStatement = sbIgnore.ToString();
            }

            var select = String.Format("SELECT * FROM [{0}$] {1}", sheetName.Replace("$", ""), ignoreStatement);

            var sheetCommand = new OleDbCommand(select, oleDbConnection);
            var sheetAdapter = new OleDbDataAdapter(sheetCommand);

            sheetAdapter.Fill(dataTable);

            return dataTable;
        }

        #endregion

        #region GetSheetNames Members

        public string[] GetSheetNames()
        {
            return ExcelHelper.GetSheetNames(oleDbConnection);
        }

        public DataColumn[] GetSheetDataColumns()
        {
            var fileSheets = GetSheetNames();
            return GetSheetDataColumns(fileSheets[0]);
        }

        public DataColumn[] GetSheetDataColumns(string sheetName)
        {
            if (string.IsNullOrEmpty(sheetName))
                throw new ArgumentException("sheetName");

            return ExcelHelper.GetSheetDataColumns(oleDbConnection, sheetName);
        }

        #endregion
    }
}